package rpt.yto.flex.remote.data.dao.processor;

public class Query_totalpsn {

	private static Query_totalpsn query = new Query_totalpsn();
	
	private Query_totalpsn() {
		
	}
	
	public static Query_totalpsn getInstance() {
		
		return query;
	}
	
	public String getQuerySQL(String period) {
		
		StringBuilder builder = new StringBuilder();
		
		builder.append(" select rownum rowno , temp.* from (  ");
		builder.append(" select distinct unitname3 unitname , count(1) total ,    ");
		builder.append(" count(case when sex = '男' then 1 end) totalman ,   ");
		builder.append(" count(case when sex = '女' then 1 end) totalwoman ,    ");
		builder.append(" count(case when isworkingunit = 'Y' then 1 end) dirunitcount ,   ");
		builder.append(" count(case when nvl(isworkingunit , 'N') = 'N' then 1 end) joinunitcount ,   ");
		builder.append(" count(case when jobname like '%经理' or jobname like '%总监' or jobname like '%总裁' or jobname like '%主管' or jobname like '%高级专员'  or  jobname like '%专家' then 1 end) compcount ,   ");
		builder.append(" count(case when jobname not like '%经理' and jobname not like '%总监' and jobname not like '%总裁'  and jobname not like '%主管' and jobname like '%高级专员'  and  jobname like '%专家' and jobname not like '操作%' and jobname not like '%业务员' and jobname not like '%客%' and jobname not like '%驾驶员' and  jobname not like '食堂%' and  jobname not like '保洁%' and jobname not like '绿化%' and  jobname not like '水电%' then 1 end) officecount  ,  "); 
		builder.append(" count(case when jobname like '%业务员' then 1 end) clerkcount ,    ");
		builder.append(" count(case when jobname like '操作%' then 1 end) opercount ,   ");
		builder.append(" count(case when jobname like '%客%' then 1 end) as attendcount ,   ");
		builder.append(" count(case when jobname like '%驾驶员' then 1 end) pilotcount ,   ");
		builder.append(" count(case when jobname like '食堂%' or  jobname like '保洁%' or jobname like '绿化%' or  jobname like '水电%' then 1 end) othercount   ");
		builder.append(" from ( select distinct psn.psncode , psn.unitcode , org_corp.unitname3 , psn.sex , psn.isworkingunit , psn.jobname  ");
		builder.append(" from vw_yto_psn psn ,  vw_yto_corp_level org_corp , vw_yto_dstction_region regions    ");
		builder.append(" where psn.pk_corp = org_corp.pk_corp(+)  ");
		builder.append(" and psn.pk_corp = regions.pk_corp1(+)  and ((psnclscope = '0' and psn.outdutydate is null) or psn.outdutydate > '"+period+"~')  and psn.indutydate <= '" + period + "~' and (isseal = 'N') ) ");
		builder.append(" group by unitname3 order by unitname3) temp   ");
		
		return builder.toString();
		
	}
	
}
